package cs5226.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import cs5226.model.SingleValueStatistics;
import cs5226.util.StringUtil;

public class AWRStatisticsDAO {
	static Log log = LogFactory.getLog(AWRStatisticsDAO.class);
			
	public static final int DAYSLATEST = 1;

	public static <T extends SingleValueStatistics> List<T> searchAWRStatistics(Class<T> c, String exprStat, String awrTable, int daysLatest) throws SQLException {
		return searchAWRStatistics (c, exprStat, awrTable, daysLatest, null);
	}
	
	public static <T extends SingleValueStatistics> List<T> searchAWRStatistics(Class<T> c, String exprStat, String awrTable, int daysLatest, String whereFilter) throws SQLException {
		String query = "SELECT " + exprStat + " statistics, a.SNAP_ID, b.BEGIN_INTERVAL_TIME, b.END_INTERVAL_TIME " +
				" FROM " + awrTable + " a JOIN DBA_HIST_SNAPSHOT b ON a.SNAP_ID = b.SNAP_ID " +
				" WHERE SYSDATE - " + daysLatest + " < b.END_INTERVAL_TIME " +
				(! StringUtil.isNullOrBlank(whereFilter) ? " AND " + whereFilter : "") +
				" GROUP BY a.SNAP_ID, b.BEGIN_INTERVAL_TIME, b.END_INTERVAL_TIME " +
				" ORDER BY a.SNAP_ID DESC";
		log.info("==searchAWRStatistics' query: " + query);
		
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		ArrayList<T> statList = new ArrayList<T>();

		try {
			conn = DBUtil.getConnection();
			stmt = conn.prepareStatement(query);
			rs = stmt.executeQuery();

			while (rs.next()) {				
				statList.add((new SingleValueStatistics(rs.getTimestamp("END_INTERVAL_TIME"), rs.getDouble("statistics"))).toT(c));
			}
		} finally {
			DBUtil.close(rs, stmt, conn);
		}

		return statList;
	}
	
	public static <T extends SingleValueStatistics> List<T> searchAWRStatisticsWithinAPeriod(String fromDate, String toDate, Class<T> c, String exprStat, String awrTable, int daysLatest, String whereFilter) throws SQLException {
		String query = "SELECT " + exprStat + " statistics, a.SNAP_ID, b.BEGIN_INTERVAL_TIME, b.END_INTERVAL_TIME " +
				" FROM " + awrTable + " a JOIN DBA_HIST_SNAPSHOT b ON a.SNAP_ID = b.SNAP_ID " +
				" WHERE TRUNC(b.END_INTERVAL_TIME) BETWEEN to_date(?, 'DD/MM/YYYY') AND to_date(?, 'DD/MM/YYYY') " +	//only difference from searchAWRStatistics
				(! StringUtil.isNullOrBlank(whereFilter) ? " AND " + whereFilter : "") +
				" GROUP BY a.SNAP_ID, b.BEGIN_INTERVAL_TIME, b.END_INTERVAL_TIME " +
				" ORDER BY a.SNAP_ID DESC";
		log.info("==searchAWRStatistics' query: " + query);
		
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		ArrayList<T> statList = new ArrayList<T>();

		try {
			conn = DBUtil.getConnection();
			stmt = conn.prepareStatement(query);
			stmt.setString(1, fromDate);
			stmt.setString(2, toDate);
			rs = stmt.executeQuery();

			while (rs.next()) {				
				statList.add((new SingleValueStatistics(rs.getTimestamp("END_INTERVAL_TIME"), rs.getDouble("statistics"))).toT(c));
			}
		} finally {
			DBUtil.close(rs, stmt, conn);
		}

		return statList;
	}
	
	public static <T extends SingleValueStatistics> List<T> searchAWRStatisticsLatest(Class<T> c, String exprStat, String awrTable, int daysLatest, String whereFilter) throws SQLException {
		String query = "SELECT " + exprStat + " statistics, a.SNAP_ID, b.BEGIN_INTERVAL_TIME, b.END_INTERVAL_TIME " +
				" FROM " + awrTable + " a JOIN DBA_HIST_SNAPSHOT b ON a.SNAP_ID = b.SNAP_ID " +
				" WHERE a.SNAP_ID = (select max (SNAP_ID) FROM DBA_HIST_SNAPSHOT) " +	//only difference from searchAWRStatistics
				(! StringUtil.isNullOrBlank(whereFilter) ? " AND " + whereFilter : "") +
				" GROUP BY a.SNAP_ID, b.BEGIN_INTERVAL_TIME, b.END_INTERVAL_TIME " +
				" ORDER BY a.SNAP_ID DESC";
		log.info("==searchAWRStatistics' query: " + query);
		
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		ArrayList<T> statList = new ArrayList<T>();

		try {
			conn = DBUtil.getConnection();
			stmt = conn.prepareStatement(query);
			rs = stmt.executeQuery();

			while (rs.next()) {				
				statList.add((new SingleValueStatistics(rs.getTimestamp("END_INTERVAL_TIME"), rs.getDouble("statistics"))).toT(c));
				
				System.out.println (new SingleValueStatistics(rs.getTimestamp("END_INTERVAL_TIME"), rs.getDouble("statistics")));
			}
		} finally {
			DBUtil.close(rs, stmt, conn);
		}

		return statList;
	}
}
